//数据库语句文件
var sql = {
    admin: {
        //管理员登录
        selAdmin: 'select * from admin where admin = ?',
        //获取用户信息
        selAllUser: 'select * from user',
        //获取所有茶叶信息
        selAllTea: 'select tea.teaId,tea.imgPath,name,tea.type,brand,price,store,username from tea,user where tea.userId = user.id',
        //查询所有订单
        selAllOrder: `select orderform.id,tea.imgPath,username,tea.name,buyTime,orderform.money,orderform.number,orderform.status,estimation,
                        estatus,orderform.address,orderform.phone,receiver from orderform,user,tea where orderform.userId=user.id
                         and orderform.teaId = tea.teaId`,
        //查询用户名称
        selUserName: 'select username from user where id=?',
        //查询茶叶名称
        selTeaName: 'select name from tea where teaId = ?',
        //删除指定用户
        deleteUser: 'delete from user where id = ?',
        //删除指定茶叶
        deleteTea: 'delete from tea where teaId=?',
        //删除指定订单
        deleteOrder: 'delete from orderform where id =?',
        //更新用户信息
        updateUser: 'update user set username=?,password=?,paypwd=?,gender=?,email=?,phone=?,type=?,money=?,address=? where id=?',
        //更新茶叶信息
        updateTea:'update tea set name=?,brand=?,type=?,price=?,store=? where teaId=?',
        //更新订单信息
        updateOrder:`update orderform set number=?,money=?,status=?,estatus=?,receiver=?,phone=?,address=? where id=?`
        
    },
    user: {
        //查询所有茶叶
        selAllTea: 'select * from tea',
        //根据用户名查询用户信息
        selUser: 'select * from user where username=?',
        //根据id查询用户信息
        selUserInfoById: 'select * from user where id=?',
        //获取用户头像
        selUserPortrait:'select imgPath from user where id=?',
        //查询所有用户
        selAllUser: 'select * from user',
        //注册普通用户
        normalRegister: 'insert into user(username,password,paypwd,gender,email,phone,address,type) values (?,?,?,?,?,?,?,?)',
        //注册商家用户
        merchantRegister: 'insert into user(username,password,paypwd,gender,email,phone,address,type,userType) values (?,?,?,?,?,?,?,?,?)',
        //红茶信息
        blackTea: 'select teaId,name,store,username,price,tea.imgPath,brand,user.id from tea right join user on tea.userId = user.id where tea.type = "红茶"',
        //黑茶信息
        darkTea: 'select teaId,name,store,username,price,tea.imgPath,brand,user.id from tea right join user on tea.userId = user.id where tea.type = "黑茶"',
        //绿茶信息
        greenTea: 'select teaId,name,store,username,price,tea.imgPath,brand,user.id from tea right join user on tea.userId = user.id where tea.type = "绿茶"',
        //乌龙茶信息
        oolongTea: 'select teaId,name,store,username,price,tea.imgPath,brand,user.id from tea right join user on tea.userId = user.id where tea.type = "乌龙茶"',
        //白茶信息
        whiteTea: 'select teaId,name,store,username,price,tea.imgPath,brand,user.id from tea right join user on tea.userId = user.id where tea.type = "白茶"',
        //黄茶信息
        yellowTea: 'select teaId,name,store,username,price,tea.imgPath,brand,user.id from tea right join user on tea.userId = user.id where tea.type = "黄茶"',
        //查询用户余额
        getUserMoney: 'select money from user where id = ?',
        //更新用户余额
        setMoney: 'update user set money=money-? where id=?', 
        //查询用户密码
        getUserPaypwd: 'select paypwd from user where id=?',
        //更新用户密码
        modifyPayPwd: 'update user set paypwd=? where id=?',
        //更新用户信息
        updateUserInfo: 'update user set password=?,gender=?,email=?,phone=?,address=? where id=?',
        //查询用户手机号
        selectAllPhone: 'select phone from user',
        //获取商家的茶叶商品
        getUserProducts: 'select * from tea where userId = ?',
        //更新用户库存量
        updateStore: 'update tea set store=store+? where teaId=?',
        //下架茶叶商品
        deleteProduct: 'delete from tea where teaId=? ',
        //发货后修改商家的金额
        modifyMoney: `update user set money = money+? where id=?`,
        //发货前获取商家茶叶的库存
        getStore:`select store from tea where teaId = ?`,
        //发货后减少库存量
        declineTeaStore: `update tea set store = store-? where teaId=?`,
        //添加新款茶叶
        addNewTea: 'insert into tea(name,type,brand,price,industry,store,userId) values(?,?,?,?,?,?,?)',
        //查询热销茶叶商品
        selHotSale: `select tea.imgPath,tea.teaId,tea.name,tea.brand,tea.price,user.username,orderform.number,user.id
                    from tea inner join orderform on tea.teaId=orderform.teaId
                    inner join user on tea.userId=user.id where tea.store>0 order by orderform.number desc`,
        //查询搜索的茶叶商品
        selSearchProduct: `select tea.imgPath,store,tea.teaId,tea.name,tea.brand,tea.price,user.username,user.id from tea
                          inner join user on tea.userId=user.id where tea.name like ?`,
        //根据id获取销售情况
        selSaleResult: `select orderform.number,tea.imgPath,tea.name,orderform.buyTime from orderform inner join tea on orderform.teaId= tea.teaId
                        where tea.userId=?`,
        //根据userId获取茶叶销售情况
        selSaleCondition: `select orderform.address,orderform.receiver,orderform.phone,orderform.status,orderform.id,tea.name,tea.imgPath,number,orderform.money,buyTime,username from orderform,tea,user where 
                            orderform.teaId=tea.teaId and orderform.userId=user.id and tea.userId=? order by status desc`,
        //修改用户头像
        modifyPortrait: 'update user set imgPath = ? where id=?',
        //获取用户的地址和手机信息
        getAddress:'select username,address,phone from user where id=?'

    },
    order: {
        //添加新订单
        insertOrder: 'insert into orderform (userId,teaId,buyTime,money,number,status,estatus,address,phone,receiver) values (?,?,?,?,?,?,?,?,?,?)',
        //修改商家的金额
        updateMoney:`update user set user.money=user.money+? where user.id=?`,
        //查询所有订单
        selectOrder: `select * from orderform`,
        //取消订单
        cancelOrder: `delete from orderform where id = ?`,
        //发货，修改订单状态
        modifyStatus: `update orderform set status = 1 where id =?`,
        //发货后修改库存需获取茶叶id
        selTeaId:`select teaId from orderform where id = ?`,
        //取消订单退回金额
        refund: `update user set money = money+? where id=?`,
        //添加评论
        setEstimation: `update orderform set estimation = ?,estatus=1 where id=?`,
        //查询评论
        getEstimation:`select estimation from orderform where id = ?`,
        //查询用户订单
        selectUserOrder: `select orderform.estatus,orderform.id,orderform.buyTime,orderform.money,orderform.status,orderform.number,tea.imgPath,tea.name,tea.brand,
                         user.username,orderform.address,orderform.receiver,orderform.phone from orderform,tea,user where orderform.teaId = tea.teaId and user.id = tea.userId and 
                         orderform.userId=?`,
        //查询茶叶的数量，用于做表格
        selectTeaNum: `select type from tea where teaId =?`,
        //查询商家的茶叶的库存量
        selectTeaStore: `select name,store from tea where userId = ?`,
        //查询要添加到购物车的茶叶的库存量
        selectOneTeaStore: `select store from tea where teaId = ?`,       
    }
} 

//暴露sql
module.exports = sql